const db = require("../../lib/conn_oracle")

//获取检测记录数据
exports.getJcDataWriteInfo = async (req, res) => {
    // console.log(req.body);
    let { form } = req.body
    let { jcYear, lines, stations, isFinish, status, description, subsys } = form
    let resDatas
    let msg
    let resStatus
    let jcSqlDatas = []
    //年份条件    
    let fullJcYear
    fullJcYear = `${jcYear}-12-31`
    jcSqlDatas.push(fullJcYear)
    // console.log(fullJcYear) 
    let jcYearWhere = `schedfinish = trunc(to_timestamp(:fullJcYear,'yyyy-mm-dd hh24:mi:ss.ff'))`
    //线路条件
    let linesWhere
    if (lines && lines.length > 1) {
        linesWhere = `and cust_linenum in (`
        for (let i = 0; i < lines.length; i++)
            linesWhere += (i > 0) ? ", :" + i : ":" + i
        linesWhere += ")"
        jcSqlDatas.push(...lines)
    } else if (lines && lines.length === 1) {
        linesWhere = `and cust_linenum = :lines`
        jcSqlDatas.push(...lines)
    } else { linesWhere = "" }
    //站点条件
    let stationsWhere
    if (stations && stations.length > 1) {
        stationsWhere = `and location in (`
        for (let i = 0; i < stations.length; i++)
            stationsWhere += (i > 0) ? ", :" + i : ":" + i
        stationsWhere += ")"
        jcSqlDatas.push(...stations)
    } else if (stations && stations.length === 1) {
        stationsWhere = `and location = :stations`
        jcSqlDatas.push(...stations)
    } else { stationsWhere = "" }
    //完成条件
    let isFinishWhere
    if (isFinish === "") {
        isFinishWhere = ""
    } else if (!Number(isFinish)) {
        isFinishWhere = `and finishdate is null`
    } else if (Number(isFinish)) {
        isFinishWhere = `and finishdate is not null`
    }
    //状态条件
    let statusWhere
    if (status.length > 0) {
        statusWhere = `and status = :status`
        jcSqlDatas.push(status)
    } else { statusWhere = "" }
    //描述条件
    let descLike
    let descWhere
    if (description.length > 0) {
        descLike = `%${description}%`
        descWhere = `and description like :descLike`
        jcSqlDatas.push(descLike)
    } else {
        descLike = ""
        descWhere = ""
    }
    //子系统条件
    let subsysWhere
    if (subsys && subsys.length > 1) {
        subsysWhere = `and cust_subsys in (`
        for (let i = 0; i < subsys.length; i++)
            subsysWhere += (i > 0) ? ", :" + i : ":" + i
        subsysWhere += ")"
        jcSqlDatas.push(...subsys)
    } else if (subsys && subsys.length === 1) {
        subsysWhere = `and cust_subsys = :subsys`
        jcSqlDatas.push(...subsys)
    } else { subsysWhere = "" }
    //查询SQL
    let jcSql = `select WONUM
                    ,DESCRIPTION
                    ,AREA
                    ,AREA2
                    ,ISPASS
                    ,to_char(FINISHDATE,'yyyy-mm-dd') as FINISHDATE
                    ,PERSON
                    ,PERSON2
                    ,PERSON3
                    ,REMARK
                    ,CUST_LINENUM
                    ,CUST_SPECNUM
                    ,SPEC_DESC
                    ,CUST_SUBSYS
                    ,SUB_DESC
                    ,LOCATION
                    ,PERSONGROUP
                    ,PGNAME
                    ,SCHEDFINISH
                    ,DEVICENUM
                    ,SBDESC
                    ,STATUS 
                 from c##wxw.xfjiance 
                 where 
                 ${jcYearWhere}  
                 ${linesWhere} 
                 ${stationsWhere} 
                 ${isFinishWhere} 
                 ${statusWhere} 
                 ${descWhere} 
                 ${subsysWhere}
                 order by schedfinish,devicenum
                 `
    // console.log(jcSqlDatas)
    // console.log(jcSql)
    try {
        let jcDataInfo = jcSqlDatas.length ? await db.search(jcSql, jcSqlDatas) : await db.search2(jcSql)
        resDatas = { jcDataInfo }
        msg = resDatas.jcDataInfo.errorNum ? "查询失败" : "查询成功"
        resStatus = resDatas.jcDataInfo.errorNum ? 0 : 1
        res.json({
            status: resStatus,
            msg,
            resDatas
        })
    } catch (err) {
        // console.log(err)
        res.json({
            status: 0,
            msg: "查询失败",
            resDatas: { err }
        })
    }

}

//批量修改检测记录
exports.batchJcDataWrite = async (req, res) => {
    // console.log(req.body);
    let { form } = req.body
    let resDatas
    let msg
    let resStatus
    let options = { autoCommit: true }
    let writeSql = `update c##wxw.xfjiance 
                    set ispass = :ISPASS,
                    finishdate = trunc(to_timestamp(:FINISHDATE,'yyyy-mm-dd hh24:mi:ss.ff'))+1-1/24,
                    person = :PERSON,
                    person2 = :PERSON2,
                    person3 = :PERSON3,
                    remark = :REMARK
                    where wonum = :WONUM`
    try {
        let batchJcDataWrite = await db.writeManyTab(writeSql, form, options)
        resDatas = { batchJcDataWrite }
        msg = resDatas.batchJcDataWrite.errorNum ? "修改失败" : "修改成功"
        resStatus = resDatas.batchJcDataWrite.errorNum ? 0 : 1
        res.json({
            status: resStatus,
            msg,
            resDatas
        })
    } catch (err) {
        console.log(err)
        res.json({
            status: 0,
            msg: "修改失败",
            resDatas: { err }
        })
    }
}